from flask_sqlalchemy import SQLAlchemy
import logging
from app.models import storage  # 导入原有的存储模块

# 创建SQLAlchemy实例
db = SQLAlchemy()
logger = logging.getLogger('AuthAPI')

def init_mysql(app):
    """初始化MySQL连接"""
    try:
        # 初始化SQLAlchemy
        db.init_app(app)
        
        # 测试连接
        with app.app_context():
            conn = db.engine.connect()
            conn.close()
            logger.info(f"MySQL连接成功: {app.config['SQLALCHEMY_DATABASE_URI']}")
            
            # 导入模型 (移到函数末尾以避免循环导入)
            
            # 创建表
            db.create_all()
            
            # 迁移设备数据
            migrate_devices_to_mysql(app)
            
            # 迁移用户设备绑定数据
            migrate_user_devices_to_mysql(app)
            
        return True
    except Exception as e:
        logger.error(f"MySQL连接失败: {str(e)}")
        return False

def migrate_devices_to_mysql(app):
    """将设备数据从文件迁移到MySQL"""
    from app.models.device import Device
    
    try:
        with app.app_context():
            # 检查设备表是否为空
            if Device.query.first() is None:
                # 迁移设备数据
                for device_id, device_info in storage.devices_db.items():
                    device = Device(
                        device_id=device_id,
                        signature_secret=device_info.get('signature_secret', 'device-secret-key'),
                        status=device_info.get('status', 'active')
                    )
                    db.session.add(device)
                
                db.session.commit()
                logger.info(f"设备数据迁移成功，共迁移 {len(storage.devices_db)} 条记录")
            else:
                logger.info("设备表已有数据，跳过迁移")
    except Exception as e:
        logger.error(f"设备数据迁移失败: {str(e)}")
        db.session.rollback()

def migrate_user_devices_to_mysql(app):
    """将用户设备绑定数据从文件迁移到MySQL"""
    from app.models.user import User
    from app.models.device import Device
    from app.models.user_device import UserDevice
    
    try:
        with app.app_context():
            # 检查用户设备绑定表是否为空
            if UserDevice.query.first() is None:
                # 加载用户设备绑定数据
                user_devices = storage.load_user_device_from_file()
                
                # 迁移用户设备绑定数据
                for user_id, binding in user_devices.items():
                    device_id = binding.get('deviceId')
                    
                    # 查找用户和设备
                    user = User.query.get(int(user_id))
                    device = Device.query.filter_by(device_id=device_id).first()
                    
                    if user and device:
                        # 创建绑定关系
                        user_device = UserDevice(
                            user_id=user.id,
                            device_id=device.id
                        )
                        db.session.add(user_device)
                
                db.session.commit()
                logger.info(f"用户设备绑定数据迁移成功，共迁移 {len(user_devices)} 条记录")
            else:
                logger.info("用户设备绑定表已有数据，跳过迁移")
    except Exception as e:
        logger.error(f"用户设备绑定数据迁移失败: {str(e)}")
        db.session.rollback()